package mapper;

import entity.Per;
import entity.Rol;
import entity.User;
import mapper.imp.UserImp;
import util.DButil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class userMapper implements UserImp {
    public int insert(User user) {
        int num=0;
        try (Connection conn = DButil.getConnection()) {
            String sql="insert into user values (?,?)";
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setInt(1,user.getId());
            ps.setString(2,user.getName());
            num=ps.executeUpdate();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return num;
    }

    public int delete(int id){
        int num=0;
        try (Connection conn=DButil.getConnection()){
            String sql="delete from user where id=?";
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setInt(1,id);
            num=ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return num;
    }

    public int update (User user){
        int num=0;
        try(Connection conn=DButil.getConnection()) {
            String sql="update  user set name=? where id=?";
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setString(1,user.getName());
            ps.setInt(2,user.getId());
            num=ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return num;
    }

    @Override
    public List<Rol> selectRol(String name) {
        List<Rol> list=new ArrayList<>();
        try (Connection conn= DButil.getConnection()){
            String sql="SELECT r.role FROM rol r,user u,u_r ur WHERE u.id=ur.uid AND r.id=ur.uid AND u.name=?";
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setString(1,name);
            ResultSet rs=ps.executeQuery();
            Rol rol=new Rol(rs.getInt("id"),rs.getString("role"));
            while (rs.next()){
                list.add(rol);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }

    @Override
    public List<Per> selectPer(String name) {
        List<Per> list = new ArrayList<>();
        try (Connection conn = DButil.getConnection()) {
            String sql = "SELECT p.permission FROM per p,rol r,user u,u_r ur,r_p rp \n" +
                    "WHERE u.id=ur.uid \n" +
                    "  AND r.id=ur.uid \n" +
                    "  AND r.id=rp.rid\n" +
                    "  AND p.id=rp.pid\n" +
                    "  AND u.name=?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, name);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Per per = new Per(rs.getInt("id"), rs.getString("permission"));
                list.add(per);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }

}
